package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.dto.MaterialDataDTO;
import com.dy.yunying.api.vo.MaterialDataVO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.Collection;
import java.util.List;
import java.util.Set;
import java.util.StringJoiner;
import java.util.stream.Collectors;

/**
 * 体验版-素材数据报表DAO
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class MaterialExperienceDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final YunYingProperties prop;

	/**
	 * 查询数据报表总数
	 *
	 * @param material
	 * @return
	 */
	public Integer count(MaterialDataVO material) {
		final String subIndent = "        ";

		final StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n");
		sql.append("    COUNT(1)\n");
		sql.append("FROM\n");
		sql.append("    (\n");
		this.appendSql(sql, material, subIndent);
		sql.append("    ) t\n");
//		log.debug("体验版-素材数据报表总数列表SQL: [\n{}]", sql.toString());

		return clickhouseTemplate.queryForObject(sql.toString(), Integer.class);
	}


	/**
	 * 查询数据报表列表
	 *
	 * @param material
	 * @param paged
	 * @return
	 */
	public List<MaterialDataDTO> list(MaterialDataVO material, boolean paged) {
		final StringBuilder sqlBuilder = new StringBuilder();
		this.appendSql(sqlBuilder, material, StringUtils.EMPTY);

		final Integer period = material.getPeriod();
		if ((null != period && 4 != period) || CollectionUtils.isNotEmpty(material.getGroupBys())) {
			sqlBuilder.append("ORDER BY\n");
			sqlBuilder.append("    ").append(this.getOrderBys(material)).append('\n');
		}
		if (paged) {
			sqlBuilder.append("LIMIT\n");
			sqlBuilder.append("    ").append(material.offset()).append(", ").append(material.getSize()).append('\n');
		}

		final String sql = sqlBuilder.toString();
		log.debug("体验版-素材数据报表分页列表SQL: [\n{}]", sql);

		long start = System.currentTimeMillis();
		final List<MaterialDataDTO> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(MaterialDataDTO.class));
		long end = System.currentTimeMillis();
		log.info("体验版-素材数据报表分页列表查询耗时: {}ms", end - start);

		return list;
	}

	private void appendSql(StringBuilder sql, MaterialDataVO material, String indent) {
		final String subIndent = indent + "        ";
		sql.append(indent).append("SELECT\n");
		sql.append(indent).append("    ").append(this.getQuotaGroupBys(material)).append(",\n");
		sql.append(indent).append("    COALESCE(promotionNum, 0) AS creativeNum, -- 创意总数\n");
		sql.append(indent).append("    COALESCE(usageDays, 0) AS materialUsageDays, -- 持续使用天数\n");
		sql.append(indent).append("    COALESCE(usageNum, 0) AS materialUsageNum, -- 素材被使用次数\n");
		sql.append(indent).append("    COALESCE(showNum, 0) AS showNum, -- 展示数\n");
		sql.append(indent).append("    COALESCE(clickNum, 0) AS clickNum, -- 点击数\n");
		sql.append(indent).append("    toDecimal64(COALESCE(rudeCost, 0), 2) AS rudeCost, -- 原始消耗\n");
		sql.append(indent).append("    toDecimal64(COALESCE(cost, 0), 2) AS cost, -- 返点后消耗\n");
		sql.append(indent).append("    COALESCE(activeNum, 0) AS activeNum, -- 设备激活数\n");
		sql.append(indent).append("    COALESCE(registerNum, 0) AS registerNum, -- 设备注册数\n");
		sql.append(indent).append("    toDecimal64(COALESCE(newPayFee2, 0), 2) AS newPayFee, -- 新增付费实付金额\n");
		sql.append(indent).append("    toDecimal64(COALESCE(newPayGivemoney2, 0), 2) AS newPayGivemoney, -- 新增付费代金券金额\n");
		sql.append(indent).append("    COALESCE(newPayNum, 0) AS newPayNum, -- 新增付费设备数\n");
		sql.append(indent).append("    toDecimal64(COALESCE(totalPayFee2, 0), 2) AS totalPayFee, -- 累计付费实付金额\n");
		sql.append(indent).append("    toDecimal64(COALESCE(totalPayGivemoney2, 0), 2) AS totalPayGivemoney, -- 累计付费代金券金额\n");
		sql.append(indent).append("    COALESCE(retention2Num, 0) AS retention2Num, -- 次留设备数\n");
		sql.append(indent).append("    COALESCE(duplicateDeviceCount, 0) AS duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indent).append("    COALESCE(retention7Num, 0) AS retention7Num, -- 7留设备数\n");
		sql.append(indent).append("    COALESCE(retention30Num, 0) AS retention30Num, -- 30留设备数\n");
		sql.append(indent).append("    toDecimal64(IF(promotionNum > 0, round(usageNum * 100 / IF(promotionNum = 0, 1, promotionNum), 2), 0), 2) AS materialUsageRate, -- 素材使用率(%)\n");
		sql.append(indent).append("    toDecimal64(IF(showNum > 0, round(clickNum * 100 / IF(showNum = 0, 1, showNum), 2), 0), 2) AS showClickRate, -- 展示点击率(%)\n");
		sql.append(indent).append("    toDecimal64(IF(clickNum > 0, round(activeNum * 100 / IF(clickNum = 0, 1, clickNum), 2), 0), 2) AS clickActiveRate, -- 点击激活率(%)\n");
		sql.append(indent).append("    toDecimal64(IF(clickNum > 0, round(registerNum * 100 / IF(clickNum = 0, 1, clickNum), 2), 0), 2) AS clickRegisterRate, -- 点击注册率(%)\n");
		sql.append(indent).append("    toDecimal64(IF(registerNum > 0, round(cost / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS registerCost, -- 注册成本\n");
		sql.append(indent).append("    toDecimal64(IF(newRegPayNum > 0, round(payRetention2Num * 100 / IF(newRegPayNum > 0, newRegPayNum, 1), 2), 0), 2) AS newPayRetention2, -- 新增付费次留(%)\n");
		sql.append(indent).append("    toDecimal64(IF(registerNum > 0, round(newPayNum * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS newPayRate, -- 新增付费率(%)\n");
		sql.append(indent).append("    toDecimal64(IF(newPayNum > 0, round(cost / IF(newPayNum > 0, newPayNum, 1), 2), 0), 2) AS newPayCost, -- 新增付费成本\n");
		sql.append(indent).append("    toDecimal64(IF(cost > 0, round(totalPayFee * 100 / IF(cost > 0, cost, 1), 2), 0), 2) AS totalRoi, -- 累计ROI(%)\n");
		sql.append(indent).append("    toDecimal64(IF(cost > 0, round(newPayFee * 100 / IF(cost > 0, cost, 1), 2), 0), 2) AS firstRoi, -- 首日ROI(%)\n");
		sql.append(indent).append("    toDecimal64(IF(registerNum > 0, round(retention2Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention2, -- 次留\n");
		sql.append(indent).append("    toDecimal64(IF(registerNum > 0, round(retention7Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention7, -- 7留\n");
		sql.append(indent).append("    toDecimal64(IF(registerNum > 0, round(retention30Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention30 -- 30留\n");
		sql.append(indent).append("FROM\n");
		sql.append(indent).append("    (\n");
		this.appendQuotaSql(material, sql, subIndent);
		sql.append(indent).append("    ) quota\n");
		sql.append(indent).append("    LEFT JOIN (\n");
		this.appendPromotionNumSql(material, sql, subIndent);
		sql.append(indent).append("    ) crt USING (").append(this.getCreativeGroupBys(material)).append(")\n");
	}

	private void appendQuotaSql(MaterialDataVO material, StringBuilder sql, String indent) {
		final String subIndent = indent + "        ";

		sql.append(indent).append("SELECT\n");
		sql.append(indent).append("    toInt8(0) AS groupKey, ").append(this.getQuotaGroupBys(material)).append(",\n");
		sql.append(indent).append("    usageDays, -- 持续使用天数\n");
		sql.append(indent).append("    usageNum, -- 素材被使用次数\n");
		sql.append(indent).append("    showNum, -- 展示数\n");
		sql.append(indent).append("    clickNum, -- 点击数\n");
		sql.append(indent).append("    rudeCost, -- 原始消耗\n");
		sql.append(indent).append("    cost, -- 返点后消耗\n");
		sql.append(indent).append("    activeNum, -- 设备激活数\n");
		sql.append(indent).append("    registerNum, -- 设备注册数\n");
		sql.append(indent).append("    newPayFee1, -- 新增付费实付金额（分成前）\n");
		sql.append(indent).append("    newPayFee2, -- 新增付费实付金额（分成后）\n");
		sql.append(indent).append("    newPayGivemoney1, -- 新增付费代金券金额（分成前）\n");
		sql.append(indent).append("    newPayGivemoney2, -- 新增付费代金券金额（分成后）\n");
		sql.append(indent).append("    newPayNum, -- 新增付费设备数\n");
		sql.append(indent).append("    newRegPayNum, -- 新增注册付费设备数\n");
		sql.append(indent).append("    totalPayFee1, -- 累计付费实付金额（分成前）\n");
		sql.append(indent).append("    totalPayFee2, -- 累计付费实付金额（分成后）\n");
		sql.append(indent).append("    totalPayGivemoney1, -- 累计付费代金券金额（分成前）\n");
		sql.append(indent).append("    totalPayGivemoney2, -- 累计付费代金券金额（分成后）\n");
		sql.append(indent).append("    retention2Num, -- 次留设备数\n");
		sql.append(indent).append("    payRetention2Num, -- 付费次留设备数\n");
		sql.append(indent).append("    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indent).append("    retention7Num, -- 7留设备数\n");
		sql.append(indent).append("    retention30Num -- 30留设备数\n");
		sql.append(indent).append("FROM\n");
		sql.append(indent).append("    (\n");
		this.appendCreativeDayReportSql(material, sql, subIndent);
		sql.append(indent).append("    ) cst\n");
		sql.append(indent).append("    FULL JOIN (\n");
		this.appendDeviceRegisterSql(material, sql, subIndent);
		sql.append(indent).append("    ) reg USING (").append(this.getQuotaGroupBys(material)).append(")\n");
	}

	private void appendCreativeDayReportSql(MaterialDataVO material, StringBuilder sql, String indent) {
		sql.append(indent).append("SELECT\n");
		sql.append(indent).append("    ").append(this.getPeriodKey(material)).append(" AS ").append(this.getQuotaGroupBys(material)).append(",\n");
		sql.append(indent).append("    COUNT(DISTINCT day) AS usageDays, -- 持续使用天数\n");
		sql.append(indent).append("    COUNT(DISTINCT promotion_id) AS usageNum, -- 素材被使用次数\n");
		sql.append(indent).append("    SUM(show_num) AS showNum, -- 展示数\n");
		sql.append(indent).append("    SUM(click_num) AS clickNum, -- 点击数\n");
		sql.append(indent).append("    SUM(rude_cost) AS rudeCost, -- 原始消耗\n");
		sql.append(indent).append("    SUM(cost) AS cost -- 返点后消耗\n");
		sql.append(indent).append("FROM\n");
		sql.append(indent).append("    (\n");
		sql.append(indent).append("    		SELECT\n");
		sql.append(indent).append("      	    day, week, month, year, collect, ctype, parentchl,chl, os, material_id AS creativeId, promotion_id, show_num, click_num, rude_cost, cost\n");
		sql.append(indent).append("       	FROM\n");
		sql.append(indent).append("            	").append(prop.getMaterialDayReport()).append('\n');
		sql.append(indent).append("        	WHERE\n");
		sql.append(indent).append("            	show_num > 0 AND image_mode in ('CREATIVE_IMAGE_MODE_VIDEO','CREATIVE_IMAGE_MODE_VIDEO_VERTICA') AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(this.getChlAndOsSql(material)).append('\n');
		sql.append(indent).append("    ) cst\n");
		sql.append(indent).append("    LEFT JOIN (SELECT platform_id, ad_id, creative_id, material_id, landing_page_id AS landingPageId FROM dim_200_pangu_mysql_ad_creative_material where version_type = 2 and material_type = 1 ) cm ON cst.ctype = toInt8(cm.platform_id) AND toString(cst.creativeId) = toString(cm.creative_id)\n");
		sql.append(indent).append("    LEFT JOIN (\n");
		sql.append(indent).append("        SELECT\n");
		sql.append(indent).append("            CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType,\n");
		sql.append(indent).append("            mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName\n");
		sql.append(indent).append("        FROM\n");
		sql.append(indent).append("            dim_200_pangu_mysql_ad_material mat\n");
		sql.append(indent).append("            LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id)\n");
		sql.append(indent).append("        WHERE\n");
		sql.append(indent).append("            screen_type != 7\n");
		sql.append(indent).append("    ) mat ON cm.material_id = mat.materialId\n");
		sql.append(indent).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM  ").append(prop.getChannelUserDeptGroupTable()).append("  ) wpc ON wpc.parent_code = cst.parentchl AND wpc.chncode = cst.chl\n");
		sql.append(indent).append("WHERE\n");
		sql.append(indent).append("    1 = 1");
		if(StringUtils.isNotEmpty(material.getTagIds())){
			//根据标签直接进行
			sql.append(indent).append(" AND materialId    in ( select   relate_id   from   ").append( prop.getAdTagRelateTable()) .append(" where  deleted = 0 and  tag_id  in   (").append(material.getTagIds()).append(')').append("  )");
		}
		sql.append(indent).append(this.getMaterialWhereSql(material)).append('\n');
		sql.append(indent).append("GROUP BY\n");
		sql.append(indent).append("    ").append(this.getQuotaGroupBys(material)).append('\n');
	}

	private void appendDeviceRegisterSql(MaterialDataVO material, StringBuilder sql, String indent) {
		sql.append(indent).append("SELECT\n");
		sql.append(indent).append("    ").append(this.getPeriodKey(material)).append(" AS ").append(this.getQuotaGroupBys(material)).append(",\n");
		sql.append(indent).append("    COUNT(DISTINCT kid) activeNum, -- 设备激活数\n");
		sql.append(indent).append("    SUM(IF(LENGTH(latest_username) > 0, 1, 0)) AS registerNum, -- 新增设备注册数\n");
		sql.append(indent).append("    SUM(fee_1) AS newPayFee1, -- 新增付费实付金额（分成前）\n");
		sql.append(indent).append("    SUM(fee_1 * sharing) AS newPayFee2, -- 新增付费实付金额（分成后）\n");
		sql.append(indent).append("    SUM(givemoney_1) AS newPayGivemoney1, -- 新增付费代金券金额（分成前）\n");
		sql.append(indent).append("    SUM(givemoney_1 * sharing) AS newPayGivemoney2, -- 新增付费代金券金额（分成后）\n");
		sql.append(indent).append("    SUM(IF(first_pay_day = day, 1, 0)) AS newPayNum, -- 新增付费设备数\n");
		sql.append(indent).append("    SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, 1, 0)) newRegPayNum, -- 新增注册付费设备数\n");
		sql.append(indent).append("    SUM(fee_total) AS totalPayFee1, -- 累计付费实付金额（分成前）\n");
		sql.append(indent).append("    SUM(fee_total * sharing) AS totalPayFee2, -- 累计付费实付金额（分成后）\n");
		sql.append(indent).append("    SUM(givemoney_total) AS totalPayGivemoney1, -- 累计付费代金券金额（分成前）\n");
		sql.append(indent).append("    SUM(givemoney_total * sharing) AS totalPayGivemoney2, -- 累计付费代金券金额（分成后）\n");
		sql.append(indent).append("    SUM(is_2_retention) AS retention2Num, -- 次留设备数\n");
		sql.append(indent).append("    SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, is_2_retention, 0)) AS payRetention2Num, -- 付费次留设备数\n");
		sql.append(indent).append("    SUM(duplicate_device) duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indent).append("    SUM(is_7_retention) AS retention7Num, -- 7留设备数\n");
		sql.append(indent).append("    SUM(is_30_retention) AS retention30Num -- 30留设备数\n");
		sql.append(indent).append("FROM\n");
		sql.append(indent).append("    (\n");
		sql.append(indent).append("        SELECT\n");
		sql.append(indent).append("            reg_day day, week, month, year, collect, kid, game_main pgid1, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ctype, cid AS creativeId, mid3, mid5, latest_username,\n");
		sql.append(indent).append("            first_pay_day, fee_1, givemoney_1, fee_total, givemoney_total, is_2_retention, IF(before_last_login_time_diff > 0, 1, 0) AS duplicate_device,is_7_retention, is_30_retention\n");
		sql.append(indent).append("        FROM\n");
		sql.append(indent).append("            ").append(prop.getNinetydeviceregtable()).append('\n');
		sql.append(indent).append("        WHERE\n");
		sql.append(indent).append("            spread_type = 1 and mid3 != '' and mid3 is not null AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(this.getChlAndOsSql(material)).append('\n');
		sql.append(indent).append("    ) reg\n");
		sql.append(indent).append("    LEFT JOIN (SELECT platform_id, version_type, material_type, creative_id, material_id, landing_page_id AS landingPageId FROM dim_200_pangu_mysql_ad_creative_material where version_type = 2 and material_type = 1 ) cm ON reg.ctype = toInt8(cm.platform_id) AND reg.mid3 = toString(cm.creative_id)\n");
		sql.append(indent).append("    LEFT JOIN (\n");
		sql.append(indent).append("        SELECT\n");
		sql.append(indent).append("            CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType,\n");
		sql.append(indent).append("            mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName\n");
		sql.append(indent).append("        FROM\n");
		sql.append(indent).append("            dim_200_pangu_mysql_ad_material mat\n");
		sql.append(indent).append("            LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id)\n");
		sql.append(indent).append("        WHERE\n");
		sql.append(indent).append("            screen_type != 7\n");
		sql.append(indent).append("    ) mat ON cm.material_id = mat.materialId\n");
		sql.append(indent).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid1 = pg.id\n");
		sql.append(indent).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM  ").append(prop.getChannelUserDeptGroupTable()).append("  ) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		sql.append(indent).append("WHERE 1 = 1\n");
		if(StringUtils.isNotEmpty(material.getTagIds())){
			//根据标签直接进行
			sql.append(indent).append(" AND materialId    in ( select   relate_id   from   ").append( prop.getAdTagRelateTable()) .append(" where  deleted = 0 and  tag_id  in   (").append(material.getTagIds()).append(')').append("  )");
		}
		sql.append(indent).append(this.getMaterialWhereSql(material)).append('\n');
		sql.append(indent).append("GROUP BY\n");
		sql.append(indent).append("    ").append(this.getQuotaGroupBys(material)).append('\n');
	}

	private void appendPromotionNumSql(MaterialDataVO material, StringBuilder sql, String indent) {
		sql.append(indent).append("SELECT\n");
		sql.append(indent).append("    toInt8(0) AS ").append(this.getCreativeGroupBys(material)).append(",\n");
		sql.append(indent).append("    COUNT(DISTINCT crt.ctype, crt.promotion_id) AS promotionNum -- 素材总数\n");
		sql.append(indent).append("FROM\n");
		sql.append(indent).append("    (\n");
		sql.append(indent).append("        SELECT ctype, promotion_id, parentchl, os\n");
		sql.append(indent).append("        FROM "+ prop.getMaterialDayReport()  +" \n");
		sql.append(indent).append("        WHERE show_num > 0\n");
		sql.append(indent).append("    ) crt\n");
		sql.append(indent).append("WHERE\n");
		sql.append(indent).append("    1 = 1").append(this.getChlAndOsSql(material)).append('\n');
		sql.append(indent).append("GROUP BY\n");
		sql.append(indent).append("    ").append(this.getCreativeGroupBys(material)).append('\n');
	}

	private String getPeriodKey(MaterialDataVO material) {
		final Integer period = material.getPeriod();
		if (null == period || 4 == period) {
			return "collect";
		} else if (1 == period) {
			return "day";
		} else if (2 == period) {
			return "week";
		} else if (3 == period) {
			return "month";
		}
		return "collect";
	}

	private String getOrderBys(MaterialDataVO material) {
		StringJoiner joiner = new StringJoiner(", ");

		String orderByName = material.getOrderByName();
		String orderByDesc = material.getOrderByDesc();
		if (StringUtils.isNotBlank(orderByName)) {
			orderByDesc = StringUtils.isBlank(orderByDesc) || (!"ASC".equals(orderByDesc) && !"DESC".equals(orderByDesc)) ? "ASC" : orderByDesc;
			joiner.add(orderByName + " " + orderByDesc);
		}
		joiner.add("period DESC");

		final Collection<String> groupBys = material.getGroupBys();
		for (String columnName : groupBys) {
			if ("materialId".equals(columnName) && !"materialId".equals(orderByName)) {
				joiner.add("materialId ASC");
			} else if ("landingPageId".equals(columnName) && !"landingPageId".equals(orderByName)) {
				joiner.add("landingPageId ASC");
			} else if ("parentchl".equals(columnName) && !"parentchl".equals(orderByName)) {
				joiner.add("parentchl ASC");
			} else if ("os".equals(columnName) && !"os".equals(orderByName)) {
				joiner.add("os ASC");
			}
		}

		return joiner.toString();
	}

	private String getQuotaGroupBys(MaterialDataVO material) {
		StringJoiner joiner = new StringJoiner(", ");
		joiner.add("period");

		final Collection<String> groupBys = material.getGroupBys();
		for (String columnName : groupBys) {
			if ("materialId".equals(columnName)) {
				joiner.add("materialId").add("sellingPointId").add("sellingPointName");
			} else if ("landingPageId".equals(columnName)) {
				joiner.add(columnName);
			} else if ("parentchl".equals(columnName)) {
				joiner.add(columnName);
			} else if ("os".equals(columnName)) {
				joiner.add(columnName);
			}
		}

		return joiner.toString();
	}

	private String getCreativeGroupBys(MaterialDataVO material) {
		final StringJoiner joiner = new StringJoiner(", ");
		joiner.add("groupKey");

		final Collection<String> groupBys = material.getGroupBys();
		for (String columnName : groupBys) {
			if ("parentchl".equals(columnName)) {
				joiner.add(columnName);
			} else if ("os".equals(columnName)) {
				joiner.add(columnName);
			}
		}

		return joiner.toString();
	}

	private String getMaterialWhereSql(MaterialDataVO material) {
		final Integer materialType = material.getMaterialType();
		final Integer creatorId = material.getCreatorId();
		final Integer makerId = material.getMakerId();
		final Collection<Long> pgids = material.getPgids();
		final Integer makeType = material.getMakeType();
		final Long materialId = material.getMaterialId();
		final String materialName = StringUtils.trim(material.getMaterialName());
		final String sellingPointName = StringUtils.trim(material.getSellingPointName());
		final String materialWidth = StringUtils.trim(material.getMaterialWidth());
		final String materialHeight = StringUtils.trim(material.getMaterialHeight());
		final Long landingPageId = material.getLandingPageId();
		final String investorIds = material.getInvestorArr();
		final StringBuilder builder = new StringBuilder();
		if (null != materialType) {
			builder.append(" AND materialType = ").append(materialType);
		}
		if (null != creatorId) {
			builder.append(" AND creatorId = ").append(creatorId);
		}
		if (null != makerId) {
			builder.append(" AND makerId = ").append(makerId);
		}
		if (CollectionUtils.isNotEmpty(pgids)) {
			builder.append(" AND pgid IN (").append(pgids.stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		if (null != makeType) {
			builder.append(" AND makeType = ").append(makeType);
		}
		if (null != materialId) {
			builder.append(" AND materialId = ").append(materialId);
		}
		if (StringUtils.isNotBlank(materialName)) {
			builder.append(" AND materialName LIKE '%").append(materialName).append("%'");
		}
		if (StringUtils.isNotBlank(sellingPointName)) {
			builder.append(" AND sellingPointName LIKE '%").append(sellingPointName).append("%'");
		}
		if (StringUtils.isNotBlank(materialWidth)) {
			builder.append(" AND materialWidth = '").append(materialWidth).append("'");
		}
		if (StringUtils.isNotBlank(materialHeight)) {
			builder.append(" AND materialHeight = '").append(materialHeight).append("'");
		}
		if (null != landingPageId) {
			builder.append(" AND landingPageId = ").append(landingPageId);
		}
		if (StringUtils.isNotEmpty(investorIds)) {
			builder.append(" AND investor IN (").append(investorIds).append(")");
		}
		return builder.toString();
	}

	private String getChlAndOsSql(MaterialDataVO material) {
		final String parentchl = StringUtils.trim(material.getParentchl());
		final Integer os = material.getOs();
		final StringBuilder builder = new StringBuilder();
		if (StringUtils.isNotBlank(parentchl)) {
			builder.append(" AND parentchl = '").append(parentchl).append('\'');
		}
		if (null != os) {
			builder.append(" AND os = ").append(os);
		}
		return builder.toString();
	}

}
